﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ClassLibrary.Data;
using ClassLibrary.Entity;
using System.Data;

namespace ClassLibrary.DAO
{
    public class ItemsDAO
    {
        private DBService _db = null;
        public ItemsDAO(String ConnectionString)
        {
            _db = new DBService(ConnectionString);
        }

        public DataTable InsUp(TblItems items, int mode)
        {
            DataTable dt = null;
            try
            {
                dt = _db.ExeSP_DataTable("TblItems_InsUp",

                    new string[] {"@IDItem", "@ItemName", "@ItemNumber", "@QuanlityOnHand", "@ValueOnHand", "@ItemDescription", "@CateID", 
                                   "@BaseSellingPrice", "@TaxPercentageRate", "@ItemType", "@PriceTaxCode", "@IdLang", "@Mode" },
                    new object[] {items.ItemID, items.ItemNumber, items.ItemName, items.QuanlityOnHand, items.ValueOnHand, items.ItemDescription,
                                    items.CateID, items.BaseSellingPrice, items.TaxPercentageRate, items.ItemType, items.PriceTaxCode, items.IdLang, mode}, CommandType.StoredProcedure);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }


        public DataTable GetAll()
        {
            DataTable dt = null;
            try
            {
                dt = _db.ExeSP_DataTable
                ("select a.ItemID, a.BaseSellingPrice, a.CateID, b.CateName, a.ItemDescription, a.ItemName, a.ItemNumber, a.PriceTaxCode, a.QuanlityOnHand, a.TaxPercentageRate, a.ValueOnHand from TblItems a, TblCategories b where a.CateID = b.CateID", false, CommandType.Text);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        public DataTable GetListItems(int ItemID, string p_ItemName, string CateID, int MethodType, int? startRowIndex, int? maximumRows, string Sort)
        {
            DataTable dt = null;
            try
            {
                dt = _db.ExeSP_DataTable("sp_TblItems_Lst",
                    new string[] { "@p_ItemID", "@p_ItemName", "@CateID", "@p_Method_Type", "@RowIndex", "@MaxRows", "@Sort" },
                    new object[] { ItemID, p_ItemName, CateID, 1, startRowIndex, maximumRows, Sort },
                    CommandType.StoredProcedure);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        // Get San pham ban chay

        public DataTable Get_SP_BanChay()
        {
            DataTable dt = null;
            try
            {
                dt = _db.ExeSP_DataTable("select * from vItems where ValueOnHand in(1,2,3,4,5)", false, CommandType.Text);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;

        }

        
        // Load ITem by Group
        public DataTable GetListItems_GroupID(int ItemID, string p_ItemName, string CateID, int MethodType, int? startRowIndex, int? maximumRows, string Sort, int? idgroup)
        {
            DataTable dt = null;

            try
            {
                dt = _db.ExeSP_DataTable("sp_TblItems_Lst_GroupID",
                    new string[] { "@p_ItemID", "@p_ItemName", "@CateID", "@p_Method_Type", "@RowIndex", "@MaxRows", "@Sort", "@GroupID" },
                    new object[] { ItemID, p_ItemName, CateID, 1, startRowIndex, maximumRows, Sort, idgroup},
                    CommandType.StoredProcedure);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        // Insert Hình Ảnh
        public bool InsImageItem(TblItemImage imgItem, int mode)
        {
            bool flag = false;
            try
            {
                _db.ExeSP_NonQuery("sp_TblItemImage_Ins",
                    new string[] { "@p_ItemID", "@p_ImageURL" },
                    new object[] { imgItem.ItemID, imgItem.ImageURL },
                    CommandType.StoredProcedure);
                flag = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }
        // Lấy hình ảnh theo IDSP
        public DataTable GetImgItems(int ItemID)
        {
            DataTable dt = null;
            try
            {
                dt = _db.ExeSP_DataTable("select * from TblItemImage where ItemID = @ItemID",
                    new string[] { "@ItemID" },
                    new object[] { ItemID },
                    CommandType.Text);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        // Lấy sản phẩm theo ID
        public TblItems GetItemByID(int ItemID)
        {
            TblItems item = new TblItems();
            try
            {
                DataTable dt = _db.ExeSP_DataTable("select vItems.* from vItems left join TblItemImage on vItems.ItemID=TblItemImage.ItemID where vItems.ItemID = @ItemID",
                    new string[] { "@ItemID" },
                    new object[] { ItemID }
                    , CommandType.Text);
                foreach (DataRow dr in dt.Rows)
                {
                    item.ItemID = ItemID;

                    item.ItemName = dr["ItemName"].ToString();
                    item.ItemNumber = dr["ItemNumber"].ToString();
                    item.QuanlityOnHand = Convert.ToInt32(dr["QuanlityOnHand"]);
                    item.ValueOnHand = Convert.ToInt32(dr["ValueOnHand"]);
                    item.ItemDescription = dr["ItemDescription"].ToString();
                    item.CateID = Convert.ToInt32(dr["CateID"]);
                    item.BaseSellingPrice = Convert.ToDouble(dr["BaseSellingPrice"]);
                    item.TaxPercentageRate = Convert.ToDouble(dr["TaxPercentageRate"]);
                    item.PriceTaxCode = Convert.ToDouble(dr["PriceTaxCode"]);
                    item.ImageURL = dr["ImageURL"].ToString();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return item;
        }

        public bool UpdatePrice(int ItemID, double Price)
        {
            bool flag = false;
            try
            {
                _db.ExeSP_NonQuery("update TblItems set BaseSellingPrice = @Price where ItemID = @ItemID",
                    new string[] { "@ItemID", "@Price" },
                    new object[] { ItemID, Price },
                    CommandType.Text);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }

        public bool DeleteItem(int ItemID)
        {
            bool flag = false;
            try
            {
                _db.ExeSP_NonQuery("delete from TblItems where ItemID = @ItemID",
                    new string[] { "@ItemID" },
                    new object[] { ItemID },
                    CommandType.Text);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }

        // chi tiết san phâm
        public DataTable GetvItemByID(int ItemID, int IDGroup)
        {
            DataTable dt = null;
            try
            {
                dt = _db.ExeSP_DataTable("select vItems.* from vItems " +
                                        " where vItems.ItemID = @ItemID and IDGroup = @IDGroup",
                    new string[] { "@ItemID", "@IDGroup" },
                    new object[] { ItemID, IDGroup},
                    CommandType.Text);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        // Hien thi sp moi nhat
        public DataTable GetNewItem()
        {
            DataTable dt = null;
            try
            {
                dt = _db.ExeSP_DataTable("select top(10)* from vItems order by ItemID desc", false, CommandType.Text);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;

        }

        // Lấy sp theo cùng group
        public DataTable GetItem_Group(int groupID)
        {
            DataTable dt = null;
            try
            {
                dt = _db.ExeSP_DataTable("select * from vItems where IDGroup = @GroupID",
                    new string[] { "@GroupID" },
                    new object[] { groupID}, CommandType.Text);

            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        // tim kiêm san phẩm theo giá
        public DataTable GetItemPrice(float PriceFrom, float PriceTo, int idGroup ,int Mode)
        {
            DataTable dt = null;
            try
            {
                dt = _db.ExeSP_DataTable("sp_searchprice",
                    new string[] { "@PriceFrom", "@PriceTo", "id", "@mode" },
                    new object[] { PriceFrom, PriceTo, idGroup, Mode },
                    CommandType.StoredProcedure);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        //update so luong sanpham
        public DataTable UpdateQuanlity(int ItemId, int Quanlity)
        {
            DataTable dt = null;
            try
            {
                dt = _db.ExeSP_DataTable("update TblItems set ValueOnHand = @Quanlity where ItemID = @ItemId",
                    new string[]{"@ItemId", "@Quanlity"},
                    new object[]{ItemId, Quanlity}, CommandType.Text);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }
    }
}
